"""
SQL 综合案例，读取文件，写入MySQL数据库中
"""

from file_define import *
from data_define import Record
from pymysql import Connection

path_txt = f"{os.getcwd()}/2011年1月销售数据.txt"
path_json = f"{os.getcwd()}/2011年2月销售数据JSON.txt"

text_file_reader = TextFileReader(path_txt)
json_file_erader = JsonFileReader(path_json)

jan_data: list[Record] = text_file_reader.read_data()
feb_data: list[Record] = json_file_erader.read_data()

# 将两个月的数据合并在一起
all_data: list[Record] = jan_data + feb_data

# 构建MySQL链接
conn = Connection(
    host="localhost",
    port=3306,
    user="root",
    password="123456",
    autocommit=True  # 自动提交
)

# 获得游标对象
cursor = conn.cursor()
# 选择数据库
conn.select_db("test")
# 组织SQL语句
print(len(all_data))
sql = f"insert into orders(order_date, order_id, money, province) values "
for record in all_data:
    sql += f"('{record.date}', '{record.order_id}', '{record.money}', '{record.province}'),"

# 去掉最后一个逗号
sql = sql[:-1]
# 执行SQL语句
cursor.execute(sql)
print(f"受影响行数：{cursor.rowcount}")
# 关闭MySQL链接对象
conn.close()
